﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for SoftwareDBConnection
/// </summary>
public class SoftwareDBConnection
{
    OleDbConnection conn;
    OleDbCommand cmd;
    OleDbDataAdapter datatAdapter;
    OleDbDataReader dataReader;
    public String connString = "";


	public SoftwareDBConnection()
    {
        conn = new OleDbConnection();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["AssetDbConnection"].ConnectionString;
        //cmd = new OleDbCommand();
	}



    public Software getSoftwareByID(int software_ID)
    {


        connString = "SELECT * FROM Software WHERE Software_ID = @Software";
        cmd = conn.CreateCommand();
        cmd.CommandText = connString;

        conn.Open();

        OleDbParameter computerIDParam = new OleDbParameter();
        computerIDParam.ParameterName = "@Room";
        computerIDParam.OleDbType = OleDbType.Numeric;
        //computerIDParam.Size = 13;
        computerIDParam.Direction = ParameterDirection.Input;
        computerIDParam.Value = software_ID;

        cmd.Parameters.Add(computerIDParam);

        //myOleDbCommand.Connection.Open();
        dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        Software software = new Software();
        while (dataReader.Read())
        {
            software.software_ID = Int32.Parse(dataReader["Software_ID"].ToString());
            software.warranty_ID = Int32.Parse(dataReader["Warranty_ID"].ToString());
            software.serial_Key = dataReader["Serial_Key"].ToString();
            software.usage_Terms = dataReader["Usage_Terms"].ToString();
            software.software_Name = dataReader["Software_Name"].ToString();
            software.brand_Name = dataReader["Brand_Name"].ToString();
        }

        closeObjects();

        return software;

    }

    public DataSet getSoftwareNotInstalledDataSet(int computerID)
    {
        connString = "SELECT * FROM Software WHERE Software_ID NOT IN (SELECT Software_ID FROM Installed_Software WHERE Computers_ID = " + computerID + " );";
        DataSet ds = new DataSet();
        cmd = conn.CreateCommand();

        conn.Open();
        OleDbDataAdapter da = new OleDbDataAdapter(connString, conn);
        da.Fill(ds, "SoftwareNotInstalledTable");

        closeObjects();

        return ds;
    }

    public DataSet getSoftwareDataSet()
    {
        DataSet ds = new DataSet();
        cmd = conn.CreateCommand();

        conn.Open();
        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Software;", conn);
        da.Fill(ds, "SoftwareTable");

        closeObjects();

        return ds;
    }

    public DataSet getSoftwareDataSet(int softwareID)
    {
        DataSet ds = new DataSet();
        cmd = conn.CreateCommand();

        conn.Open();
        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Software WHERE Software_ID = " + softwareID + ";", conn);
        da.Fill(ds, "SoftwareTable");

        closeObjects();

        return ds;
    }

    public DataSet getSoftwareDataSet_ByAdvStrSearch(String searchBy, String assetID)
    {
        DataSet ds = new DataSet();

        cmd = conn.CreateCommand();

        conn.Open();
        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Software WHERE " + searchBy + " = '" + assetID + "';", conn);
        da.Fill(ds, "SoftwareTable");

        closeObjects();

        return ds;
    }

    public void updateAll(Computer computer)
    {
        String connStringUpdate = "UPDATE Computers SET Computers.License = " + computer.license + ", Computers.Computer_Name = " + computer.computer_Name + "WHERE Computers.Computer_ID = " + computer.computer_ID + ";";

        cmd = new OleDbCommand(connStringUpdate, conn);
        conn.Open();

        cmd.ExecuteNonQuery();
        closeObjects();
    }

    public int update(Computer computer)
    {
        int RecordsAffected;

        OleDbCommand myOleDbCommand;
        OleDbConnection myOleDbConnection = new OleDbConnection();
        myOleDbConnection.ConnectionString = ConfigurationManager.ConnectionStrings["AssetDbConnection"].ConnectionString;


        String connStringUpdate = "UPDATE Computers SET Computer_Name = '" + computer.computer_Name + "' WHERE Computer_ID = " + computer.computer_ID + ";";

        myOleDbCommand = new OleDbCommand(connStringUpdate, myOleDbConnection);
        myOleDbConnection.Open();

        RecordsAffected = myOleDbCommand.ExecuteNonQuery();
        myOleDbConnection.Close();

        return RecordsAffected;
    }

    public void delete(Software software)
    {
        connString = "DELETE FROM Software WHERE Software_ID = " + software.software_ID + ";";
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["AssetDbConnection"].ConnectionString;


        cmd = new OleDbCommand(connString, conn);
        conn.Open();

        cmd.ExecuteNonQuery();
        conn.Close();
    }

    public void installSoftwareOnPC(InstalledSoftware intsalledSoftware)
    {
        connString = "INSERT INTO Installed_Software (Update_Version, Update_Date, Install_Date, Computers_ID, Software_ID)" +
        "VALUES ('" + intsalledSoftware.update_Version +
             "', '" + intsalledSoftware.update_Date +
             "', '" + intsalledSoftware.install_Date +
             "', " + intsalledSoftware.computers_ID +
             ", " + intsalledSoftware.software_ID + ")";
        
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["AssetDbConnection"].ConnectionString;


        cmd = new OleDbCommand(connString, conn);
        conn.Open();

        cmd.ExecuteNonQuery();
        conn.Close();
    }

    public void closeObjects()
    {
        if (conn.State.ToString().Equals("Open"))
        {
            conn.Close();
        }
        if (cmd.Connection.State.ToString().Equals("Open"))
        {
            cmd.Connection.Close();
        }
    }



}